#coding:utf-8
#存在的问题
#①打开文件没有关闭
#②try块包围的不准确

from BeautifulSoup import BeautifulSoup
import sqlite3

cx = sqlite3.connect("StuInfo.db")
cu = cx.cursor()
cu.execute("create table StuInfo (ID varchar(20) primary key,Num varchar(20) ,Name varchar(20) , Sex varchar(4) , Nation varchar(8),Origo varchar(20),PoliticalStatus varchar(8),Erollment varchar(20),Dept varchar(20), Class varchar(20))")
list = []
ls = open("list.txt")
for i in ls.readlines():
    list.append(i.strip())
ls.close()
length = len(list)
count = 0
print u"文件列表加载完成!\n开始导入数据库..."


for htm in list:
    try:
        src = open("F:\\BaiduYunDownload\\StuInfo\\Test\\"+htm)
        soup = BeautifulSoup(src,fromEncoding="GB2312")
        t = soup.findAll("tr",attrs={"style":"height:20px;"})[0:5]
        test =[]
        for i in t:
            a = str(i).split("\n")
            for j in a:
                b = j.split("<")
                c = b[1].split(">")[1]
                #print c
                test.append(c)
        src.close()
    except Exception, e:
        print str(e)
        continue
    Name = test[2]
    Num = test[4]
    Sex = test[6]
    ID = test[8]
    Nation = test[13]
    Origo = test[15]
    PoliticalStatus = test[17]
    Birth = test[19]
    Class = test[23]
    Erollment = test[25]
    Dept = Test = test[37]
    #print Name
    try:
        cu.execute("insert into StuInfo VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" %(ID,Num,Name,Sex,Nation,Origo,PoliticalStatus,Erollment,Dept,Class))
        cx.commit()
    except Exception ,e:
        print str(e)
    #src.close()
    count += 1
    if count %100 ==0 or count == length:
        print str(int(count*1.0/length*100))+"%"

cx.close()


print "Done!"























